using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Collections.Generic;

namespace SqlServerDriver
{
	/*
		Class to encapsulate the relationship between Sql Server service broker objects 
		and generate scripts based on that.
	*/
	public class BrokerObjectCollection
	{
		// Inner class service object
		public class Service
		{
			int id;
			String name;
			ArrayList queues;
			ArrayList contracts;

			public Service(int id, String name)
			{
				this.id = id;
				this.name = name;
				this.queues = new ArrayList();
				this.contracts = new ArrayList();
			}
			public int Id { get { return this.id; } }
			public String Name { get { return this.name; } }
			public ArrayList Queues { get { return this.queues; } }
			public ArrayList Contracts { get { return this.contracts; } }

			public void AddContract(Contract c)
			{
				this.contracts.Add(c);
				c.AddService(this);
			}
			public void AddQueue(Queue q)
			{
				this.queues.Add(q);
				q.AddService(this);
			}
		}

		// Inner class queue object
		public class Queue
		{
			int id;
			String name;
			String proc;
			int maxReaders;
			bool isActivated;
			ArrayList services;

			public Queue(int id, String name)
			{
				this.id = id;
				this.name = name;
				this.services = new ArrayList();
				this.proc = "";
				this.maxReaders = 0;
				this.isActivated = false;
			}
			public int Id { get { return this.id; } }
			public String Name { get { return this.name; } }
			public bool IsActivated
			{
				get { return this.isActivated; }
				set { this.isActivated = value; }
			}
			public int MaxReaders
			{
				get { return this.maxReaders; }
				set { this.maxReaders = value; }
			}
			public String ActivationProc
			{
				get { return this.proc; }
				set { this.proc = value; }
			}
			public ArrayList Services { get { return this.services; } }

			public void AddService(Service s) { this.services.Add(s); }
		}

		// Inner class contract object
		public class Contract
		{
			int id;
			String name;
			ArrayList messages;
			ArrayList services;
			Hashtable usage = new Hashtable();
			public Contract(int id, String name)
			{
				this.id = id;
				this.name = name;
				this.messages = new ArrayList();
				this.services = new ArrayList();
			}
			public int Id { get { return this.id; } }
			public String Name { get { return this.name; } }
			public ArrayList Messages { get { return this.messages; } }
			public ArrayList Services { get { return this.services; } }

			public void AddMessage(MessageType m)
			{
				this.Messages.Add(m);
				m.AddContract(this);
			}
			public MessageType FindMessage(String name)
			{
				foreach (MessageType m in this.messages)
					if (m.Name.CompareTo(name) == 0)
						return m;
				return null;
			}

			public void AddService(Service s) { this.services.Add(s); }

			private String MakeKey(Contract c, MessageType m)
			{
				return String.Format("{0}/{1}", c.Id, m.Id);
			}
			public void AddUsage(MessageType m, String usageType)
			{
				String key = this.MakeKey(this, m);
				this.usage.Add(key, usageType);
			}
			public String GetUsage(MessageType m)
			{
				String key = this.MakeKey(this, m);
				return (String)this.usage[key];
			}
		}

		// Inner class message type object
		public class MessageType
		{
			int id;
			String name;
			ArrayList contracts;

			public MessageType(int id, String name)
			{
				this.id = id;
				this.name = name;
				this.contracts = new ArrayList();
			}
			public int Id { get { return this.id; } }
			public String Name { get { return this.name; } }
			public ArrayList Contracts { get { return this.contracts; } }
			public void AddContract(Contract c) { this.contracts.Add(c); }
		}

		// Arrays to hold collections of service broker objects
		ArrayList messages;
		ArrayList queues;
		ArrayList contracts;
		ArrayList services;

		public BrokerObjectCollection()
		{
			this.messages = new ArrayList();
			this.queues = new ArrayList();
			this.contracts = new ArrayList();
			this.services = new ArrayList();
		}

		public ArrayList Contracts { get { return this.contracts; } }
		public ArrayList MessageTypes { get { return this.messages; } }
		public ArrayList Services { get { return this.services; } }
		public ArrayList Queues { get { return this.queues; } }

		public Contract FindContract(int id)
		{
			foreach (Contract c in this.contracts)
				if (c.Id == id)
					return c;
			return null;
		}

		public Contract FindContract(String name)
		{
			foreach (Contract c in this.contracts)
				if (c.Name.CompareTo(name) == 0) return c;
			return null;
		}

		public MessageType FindMessage(int id)
		{
			foreach (MessageType m in this.messages)
				if (m.Id == id)
					return m;
			return null;
		}
		public MessageType FindMessage(String name)
		{
			foreach (MessageType m in this.messages)
				if (m.Name.CompareTo(name) == 0)
					return m;
			return null;
		}

		public Service FindService(int id)
		{
			foreach (Service s in this.services)
				if (s.Id == id)
					return s;
			return null;
		}

		public Service FindService(String name)
		{
			foreach (Service s in this.services)
				if (s.Name.CompareTo(name) == 0)
					return s;
			return null;
		}

		public Queue FindQueue(int id)
		{
			foreach (Queue q in this.queues)
				if (q.Id == id)
					return q;
			return null;
		}

		public Queue FindQueue(String name)
		{
			foreach (Queue q in this.queues)
				if (q.Name.CompareTo(name) == 0)
					return q;
			return null;
		}

		private String CleanProcName(String procNameIn)
		{
			SimpleRegex sr = new SimpleRegex(@"\[.*?\]\.\[(.*?)\]");
			if (sr.MatchText(procNameIn))
			{
				return sr[1];
			}
			else
			{
				SimpleRegex sr2 = new SimpleRegex(@"\[(.*?)\]");
				if (sr2.MatchText(procNameIn))
				{
					return sr2[1];
				}
			}
			return procNameIn;
		}

		public void Populate(String connectionString)
		{
			String sql;
			DataSet ds;
			// Select all messages
			sql = @"select message_type_id, name from sys.service_message_types";
			Helper.GenericMakeDset(connectionString, sql, "MESSAGES", out ds);
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				this.messages.Add(new BrokerObjectCollection.MessageType((int)dr[0], (String)dr[1]));
			}

			// Select all queues
			sql = @"select object_id,name,is_activation_enabled,activation_procedure,cast(max_readers as int) from sys.service_queues";
			Helper.GenericMakeDset(connectionString, sql, "QUEUES", out ds);
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				Queue q = new BrokerObjectCollection.Queue((int)dr[0], (String)dr[1]);
				// add other
				q.IsActivated = (bool)dr[2];
				String procName = ((dr[3].GetType() == typeof(System.DBNull))) ? "" : (String)dr[3];
				q.ActivationProc = this.CleanProcName(procName);
				q.MaxReaders = ((dr[4].GetType() == typeof(System.DBNull))) ? 0 : (int)dr[4];
				this.Queues.Add(q);
			}
			// Select all Services
			sql = @"select service_id, name from sys.services";
			Helper.GenericMakeDset(connectionString, sql, "SERVICES", out ds);
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				this.services.Add(new BrokerObjectCollection.Service((int)dr[0], (String)dr[1]));
			}
			// Select all Contracts
			sql = @"select service_contract_id, name from sys.service_contracts";
			Helper.GenericMakeDset(connectionString, sql, "CONTRACTS", out ds);
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				this.contracts.Add(new BrokerObjectCollection.Contract((int)dr[0], (String)dr[1]));
			}
			// Add messages to contracts
			sql = @"select service_contract_id, message_type_id from sys.service_contract_message_usages";
			Helper.GenericMakeDset(connectionString, sql, "CONTRACTS", out ds);
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				int cid = (int)dr[0];
				int mid = (int)dr[1];
				Contract c = this.FindContract(cid);
				MessageType m = this.FindMessage(mid);
				if (c != null && m != null)
					c.AddMessage(m);
			}
			// set initiators. Required when detaching a message from a contract.
			sql = @"
select c.service_contract_id as Contract, m.message_type_id as Message, 
       case when mu.is_sent_by_initiator = 1 and mu.is_sent_by_target = 1 then 'ANY'
            when mu.is_sent_by_initiator = 1 then 'INITIATOR'      
            when mu.is_sent_by_target = 1 then 'TARGET'
            else '' end as SENT_BY
from  sys.service_contracts c 
left join sys.service_contract_message_usages mu on mu.service_contract_id = c.service_contract_id
left join sys.service_message_types m on m.message_type_id = mu.message_type_id
";
			Helper.GenericMakeDset(connectionString, sql, "CONRACTS", out ds);
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				int cid = (int)dr[0];
				int mid = (int)dr[1];
				String sentBy = (String)dr[2];

				MessageType m = this.FindMessage(mid);
				Contract c = this.FindContract(cid);
				c.AddUsage(m, sentBy);
			}

			// Add contracts to services
			sql = @"select service_id, service_contract_id from sys.service_contract_usages";
			Helper.GenericMakeDset(connectionString, sql, "SERVICES", out ds);
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				int sid = (int)dr[0];
				int cid = (int)dr[1];
				Service s = this.FindService(sid);
				Contract c = this.FindContract(cid);
				if (s != null && c != null)
					s.AddContract(c);
			}
			// Add queues to services
			sql = @"select service_id, service_queue_id from sys.service_queue_usages";
			Helper.GenericMakeDset(connectionString, sql, "QUEUES", out ds);
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				int sid = (int)dr[0];
				int qid = (int)dr[1];
				Service s = this.FindService(sid);
				Queue q = this.FindQueue(qid);
				if (s != null && q != null)
					s.AddQueue(q);
			}
		}

		/*
		 *  Detach Contract from service(s) before dropping the object
		*/
		public String ScriptDropContract(String contractName)
		{
			String script = "";
			// remove contract from all services.
			Contract c = this.FindContract(contractName);
			if (c != null)
			{
				script += String.Format(@"
IF EXISTS(SELECT 1 FROM sys.service_contracts WHERE Name = '{0}')
begin
", c.Name);
				foreach (Service s in c.Services)
				{
					script += String.Format("   alter service {0} (drop contract {1})\n", s.Name, c.Name);
				}
				script += String.Format(@"    DROP CONTRACT {0}
end", c.Name);
			}
			return script;
		}

		public String ScriptDropMessage(String messageName)
		{
			String script = "";
			MessageType m = this.FindMessage(messageName);
			if (m != null)
			{
				foreach (Contract c in m.Contracts)
					script += this.ScriptDropContract(c.Name);
				script += String.Format(@"
IF EXISTS(SELECT 1 FROM sys.service_message_types WHERE Name = '{0}')
    drop message type {0}
", messageName);
				// now add back contracts
				foreach (Contract c in m.Contracts)
				{
					List<String> a = new List<String>();
					foreach (MessageType mleft in c.Messages)
					{
						if (mleft.Name.CompareTo(messageName) != 0)
						{
							String huh = c.GetUsage(mleft);
							String script2 = String.Format("{0} SENT BY {1}", mleft.Name, huh);
							a.Add(script2);
						}
					}
					if (a.Count > 0)
					{
						String clause = String.Join(",", a.ToArray());
						script += String.Format("CREATE CONTRACT {0} ({1})\n", c.Name, clause);
						foreach (Service s in c.Services)
						{
							script += String.Format("alter service {0} (add contract {1})\n", s.Name, c.Name);
						}
					}
				}
			}
			return script;
		}
		
		public String ScriptDropService(String serviceName)
		{
			Service s = this.FindService(serviceName);
			String sql = "-- Cannot drop service unless queue(s) empty\n";
			String indent = "";
			if (s != null)
			{
				foreach (Queue q in s.Queues)
				{
					sql += String.Format("{1}if not exists (select 1 from {0})\n", q.Name, indent);
					indent += "    ";
				}
				sql += String.Format("{1}drop service {0}\n", s.Name, indent);
			}
			return sql;
		}

		public String ScriptDropQueue(String queueName)
		{
			// 1. Check no messages in queue
			String script = String.Format(@"
IF EXISTS( SELECT 1 FROM sys.service_queues WHERE Name = '{0}' )
    IF NOT EXISTS ( SELECT 1 FROM [{0}] )
    BEGIN
", queueName);
			Queue q = this.FindQueue(queueName);
			if (q != null)
			{
				foreach (Service s in q.Services)
				{
					script += String.Format(@"
        IF EXISTS ( SELECT 1 FROM sys.services WHERE Name = '{0}' )
            DROP SERVICE [{0}]   
 ", s.Name);
				}
			}
			script += String.Format(@"
        DROP QUEUE [{0}]
   END
 ", queueName);
			return script;
		}
	}
}
